using System;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Globalization;

namespace Microsoft.CommerceServer.Catalog.Tools
{
	/// <summary>
	/// This class is used to interface with the SQL Database and provide appropriate results
	/// </summary>
	internal sealed class SqlHelper
	{
		#region Constants
		/// <summary>
		///	Contains the table name for the typed CatalogItems DataSet. 
		/// </summary>
		internal const string CatalogItemsDataSetTableName = "CatalogItems";
		/// <summary>
		/// Contains the name of the stored procedure used to retrive empty categories
		/// </summary>
		internal const string FilterEmptyCategoriesSpName = "dbo.ctlg_GetEmptyCategories";
		/// <summary>
		/// Set the default timeout to thirty minutes
		/// </summary>
		internal const int DefaultTimeOut = 1800;
		#endregion

		#region Constructor
		/// <summary>
		/// Private Constructor
		/// </summary>
		private SqlHelper()
		{
		}
		#endregion

		#region Internal Methods

		/// <summary>
		/// Returns the SQL server and database name given s SQL connection
		/// </summary>
		/// <param name="connection">The SQL connection</param>
		/// <param name="serverName">Out parameter retruning the SQL Server name</param>
		/// <param name="databaseName">Out parameter retruning the database name</param>
		internal static void GetServerAndDatabaseNameFromSqlConnection(SqlConnection connection,out string serverName,
			out string databaseName)
		{
			if (connection.State != ConnectionState.Open)
			{
				connection.Open();
				serverName = connection.DataSource;
				databaseName = connection.Database;
				connection.Close();
			}
			else
			{
				serverName = connection.DataSource;
				databaseName = connection.Database;
			}
		}

		/// <summary>
		/// Returns the name of the server and database given a SQL connection string.
		/// This method takes a source connnection and destination connection.
		/// If the two connections point to the same server then destinationServerName is null
		/// If the two connections point to the same server and same database then destinationServerName and destinationDatabaseName is null
		/// </summary>
		/// <param name="catalogConnection"></param>
		/// <param name="inventoryConnection"></param>
		/// <param name="destinationServerName">The server name. </param>
		/// <param name="destinationDatabaseName">The database name.</param>
		internal static void GetServerAndDatabaseName(SqlConnection catalogConnection, SqlConnection inventoryConnection, out string destinationServerName, out string destinationDatabaseName)
		{

			destinationServerName = null;
			destinationDatabaseName = null;
			// If 
			if ((catalogConnection != null) && (inventoryConnection != null))
			{
				// Get the server and database names for the source
				string sourceServerName, sourceDatabaseName;
				GetServerAndDatabaseNameFromSqlConnection(catalogConnection, out sourceServerName, out sourceDatabaseName);

				// Get the server and database names for the target
				string targetServerName, targetDatabaseName;
				GetServerAndDatabaseNameFromSqlConnection(inventoryConnection, out targetServerName, out targetDatabaseName);

				// IF the source server and target server are different then return the target server and database names
				// else set the server name to null
				if (Validations.DBCompareString(sourceServerName, targetServerName, false) != 0)
				{
					destinationServerName = targetServerName;
					destinationDatabaseName = targetDatabaseName;
				}
				// If the servers are the same but the databsese are different then return the target database name
				else if (Validations.DBCompareString(sourceDatabaseName, targetDatabaseName, false) != 0)
				{
					destinationDatabaseName = targetDatabaseName;
				}
			}
		}

		/// <summary>
		/// Execute a SqlCommand (that returns a resultset) against the database specified in the connectionString
		/// using the provided parameters.
		/// </summary>
		/// <param name="connection"> Valid ConnectringString for the database to connect to</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command</param>
		/// <param name="parameterValues">an array of paramter values used to execute the command</param>
		/// <returns>a dataset containing the resultset generated by the command</returns>
		internal static DataSet ExecuteDataset(string connectionString, CommandType commandType, string spName,
			object[] parameterValues)
		{
			//Discover the parameters for this stored procedure
			SqlParameter[] commandParameters = DiscoverSpParameterSet(connectionString, spName);

			//assign the provided values to these parameters based on parameter order
			AssignParameterValues(commandParameters, parameterValues);

			//create a command and prepare it for execution
			using (SqlCommand cmd = new SqlCommand())
			{
				PrepareCommand(cmd, new SqlConnection(connectionString), commandType, spName, commandParameters);

				//create the DataAdapter & DataSet
				SqlDataAdapter da = new SqlDataAdapter(cmd);
				DataSet ds = new CatalogItemsDataSet();
				ds.EnforceConstraints = false;
				string tableName = CatalogItemsDataSetTableName;

				//fill the DataSet using default values for DataTable names, etc.
				da.Fill(ds, tableName);

				// detach the SqlParameters from the command object, so they can be used again.
				cmd.Parameters.Clear();

				//return the dataset
				return ds;
			}
		}

		/// <summary>
		///		This method is used to take a SQLOLEDB connection string as typically found in the Commerce Server
		///		administration database and remove the "Provider=SQLOLEDB.*" part so that the connection string becomes a
		///		valid ADO.NET SqlClient connection string.
		/// </summary>
		/// <param name="connectionString">The Database connection string.</param>
		/// <param name="cleaned">An output string containing the cleaned connection string</param>
		/// <returns>A boolean indicating whether or not the connection string provided is a valid SqlClient connection string.  The
		///		output parameter is only valid when the returned value is true.</returns>
		/// <remarks>
		///		The connection strings in Admin DB contain Provider info, which works
		///		fine with ADO.NET but not for SQLclient. In this case, there is a need to
		///		remove the "Provider" part of the connection string.
		/// </remarks>
		internal static bool CleanSqlClientConnectionString(string connectionString, out string cleaned)
		{
			const string PROVIDER_TOKEN = "Provider";
			const string PROVIDER_NAME = "SQLOLEDB";
			string[] stringArr;
			string[] tokenValues;
			cleaned = null;
			StringBuilder cleanedBuilder = new StringBuilder();

			// divide the connection string into multiple clauses, specified by ';'.
			stringArr = connectionString.Split(';');

			// loop through clauses to remove one or more Provider clauses.
			foreach (string strClause in stringArr)
			{
				tokenValues = strClause.Split('=');
				// is this a Provider clause?
				if (String.Compare(tokenValues[0].Trim(), PROVIDER_TOKEN, true, CultureInfo.InvariantCulture) == 0)
				{
					//not a valid SqlOleDB provider if the provider string is empty.
					if (tokenValues.Length < 2)
						return false;

					// not a valid SqlOleDB provider if the provider string is not SQLOLEDB or SQLOLEDB.X (X is the version number)
					if (String.Compare(tokenValues[1].Trim(), 0, PROVIDER_NAME, 0, PROVIDER_NAME.Length, true,
						CultureInfo.InvariantCulture) != 0)
					{
						return false;
					}
				}
				else
				{
					if (strClause.Length != 0)
					{
						cleanedBuilder.Append(strClause);
						cleanedBuilder.Append(';');
					}
				}
			}

			cleaned = cleanedBuilder.ToString();
			return true;
		}

		/// <summary>
		///		Converts an ADO connection string to a Sql connection string. 
		///		Removes the "Provider=foo;" of the ADO connection string. 
		/// </summary>
		/// <param name="connectionString"></param>
		/// <returns></returns>
		internal static string SqlADOConnectionStringHack(string connectionString)
		{
			//Set the Persist security info to true 
			string catalogConnectionString = string.Empty;
			if (CleanSqlClientConnectionString(connectionString, out catalogConnectionString) == false)
			{
				throw new CatalogInitializationException(Errors.InvalidConnectionString);

			}
			catalogConnectionString += ";Persist Security Info=true;";
			return catalogConnectionString;
		}
		#endregion

		#region Private Methods
		/// <summary>
		/// This method is used to attach array of SqlParameters to a SqlCommand.
		/// 
		/// This method will assign a value of DbNull to any parameter with a direction of
		/// InputOutput and a value of null.  
		/// 
		/// </summary>
		/// <param name="command">The command to which the parameters will be added</param>
		/// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
		private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
		{
			SqlParameter temp = null;
			foreach (SqlParameter p in commandParameters)
			{
				//check for derived output value with no value assigned
				if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
				{
					p.Value = DBNull.Value;
				}
				temp = new SqlParameter(p.ParameterName, p.Value);
				command.Parameters.Add(temp);
			}
		}

		/// <summary>
		/// This method assigns an array of values to an array of SqlParameters.
		/// </summary>
		/// <param name="commandParameters">array of SqlParameters to be assigned values</param>
		/// <param name="parameterValues">array of objects holding the values to be assigned</param>
		private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
		{
			if ((commandParameters == null) || (parameterValues == null))
			{
				//do nothing if we get no data
				return;
			}

			// we must have the same number of values as we pave parameters to put them in
			if (commandParameters.Length != parameterValues.Length)
			{
				throw new ArgumentException(Errors.SqlHelper_ArgumentCountException);
			}

			//iterate through the SqlParameters, assigning the values from the corresponding position in the 
			//value array
			for (int i = 0, j = commandParameters.Length; i < j; i++)
			{
				// Substitute a .Net null-value to a Sql null-value. 
				if (parameterValues[i] == null)
				{
					commandParameters[i].Value = System.DBNull.Value;
				}
				else
				{
					commandParameters[i].Value = parameterValues[i];
				}
			}
		}

		/// <summary>
		/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters 
		/// to the provided command.
		/// </summary>
		/// <param name="command">the SqlCommand to be prepared</param>
		/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or T-SQL command</param>
		/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
		private static void PrepareCommand(SqlCommand command, SqlConnection connection, CommandType commandType, string commandText, SqlParameter[] commandParameters)
		{
			//if the provided connection is not open, we will open it
			if (connection.State != ConnectionState.Open)
			{
				connection.Open();
			}
			// Set the command timeout to five minutes.
			command.CommandTimeout = DefaultTimeOut;
			//associate the connection with the command
			command.Connection = connection;

			//set the command text (stored procedure name or SQL statement)
			command.CommandText = commandText;

			//set the command type
			command.CommandType = commandType;

			//attach the command parameters if they are provided
			if (commandParameters != null)
			{
				AttachParameters(command, commandParameters);
			}
			return;
		}
		

		/// <summary>
		/// Resolve at run time the appropriate set of SqlParameters for a stored procedure
		/// </summary>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="spName">the name of the stored procedure</param>
		/// <returns></returns>
		private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName)
		{
			using (SqlConnection sqlConnection = new SqlConnection(connectionString))
			{
				using (SqlCommand cmd = new SqlCommand(spName, sqlConnection))
				{
					sqlConnection.Open();
					cmd.CommandType = CommandType.StoredProcedure;

					//Retrieve all the parameters for a given stored procedure
					SqlCommandBuilder.DeriveParameters(cmd);

					//Remove the return value parameter
					cmd.Parameters.RemoveAt(0);
				
					SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

					cmd.Parameters.CopyTo(discoveredParameters, 0);

					return discoveredParameters;
				}
			}
		}
		#endregion
	}
}
